package com.icss.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import com.icss.utils.DB;
import com.icss.utils.Record;
import com.icss.utils.RecordSet;

/**
 * @author 韦锦良
 *　类的作用：逻辑处理基类
 * 最后修改时间：2010-3-20　下午12:59:09
 * 最后的改动内容：初始版本
 */
public class Dao {

	private Connection conn = null;

	private Statement stat = null;

	private ResultSet rs = null;

	//创建一个可滚动的statement
	public Dao() {

		conn = DB.getConnection();
		try {
			stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//执行sql语句
	public boolean execute(String sql) {

		try {

			if (0 != stat.executeUpdate(sql)) {
				return true;
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 执行 SQL 语句 (带分页功能)
	 * @param strSQL SQL语句
	 * @param pageSize 每页最大记录数
	 * @param currentPage 当前页号
	 * @return 如果执行的是查询操作(select ...), 成功返回封装成 RecordSet 的记录集, 异常或失败返回 null
	 */
	@SuppressWarnings("unchecked")
	public RecordSet queryPager(String strSQL, int pageSize, int currentPage) {

		PreparedStatement ps = null;

		try {
			if (pageSize <= 0)
				throw new Exception("页记录数小于 0: (" + pageSize + " 条记录/页)");
			if (currentPage <= 0)
				currentPage = 1;
			RecordSet set = new RecordSet();
			strSQL = strSQL.trim();
			ps = conn.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

			ResultSet rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int column = rsmd.getColumnCount();

			set.pageSize = pageSize;
			//			移到结果集最后一条, 取得记录总数
			rs.last();
			set.totalRecordCount = rs.getRow();

			set.totalPage = set.totalRecordCount % pageSize == 0 ? set.totalRecordCount / pageSize
					: set.totalRecordCount / pageSize + 1;

			if (currentPage > set.totalPage) {
				currentPage = set.totalPage;
			}
			set.currentPage = currentPage;
			//计算当前页有多少条记录
			if (currentPage == set.totalPage && set.totalRecordCount % pageSize != 0)
				set.currentPageSize = set.totalRecordCount % pageSize;
			else
				set.currentPageSize = pageSize;

			if (set.totalRecordCount == 0)
				return set;

			//定位到当前页的页首
			rs.absolute(pageSize * (currentPage - 1) + 1);
			do {
				Record record = new Record();
				for (int i = 0; i < column; i++) {
					String strField = rsmd.getColumnName(i + 1).toUpperCase();
					Object obj = rs.getObject(i + 1);
					if (strField.equals("PUBLISHTIME") && null != obj || strField.equals("ENTERCOMPANYTIME")
							&& null != obj || strField.equals("LEAVECOMPANYTIME") && null != obj) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						
						record.put(strField, sdf.format(obj));
					} else {
						record.put(strField, rs.getObject(i + 1));
					}
				}
				set.add(record);
			} while (rs.getRow() < pageSize * currentPage && rs.next());
			rs.close();

			return set;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (Exception e) {
			}
		}
	}

	//查询给定的sql语句，返回一个结果集
	public ResultSet query(String sql) {

		try {
			rs = stat.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return rs;

	}

	//关闭连接
	public void close() {

		try {

			if (null != conn) {
				conn.close();
			}

			if (null != stat) {
				stat.close();
			}

			if (null != rs) {
				rs.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}

	}

}
